package fy.bean;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import fy.util.DBUtil;

public class Zxhd {
	private int id;
	private String category;
	private String title;
	private String content;
	private String author;
	private Date time;
	private String phone;
	private String email;
	private String categoryname;
	private int homepage;
	
	PreparedStatement statement=null;
	ResultSet rs = null;
	Connection connection = null;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getCategoryname() {
		return categoryname;
	}
	public void setCategoryname(String categoryname) {
		this.categoryname = categoryname;
	}
	public int getHomepage() {
		return homepage;
	}
	public void setHomepage(int homepage) {
		this.homepage = homepage;
	}
	
	public boolean doAdd(String category,String title,String content,String author,String phone,String email,String txtDate) {
		boolean add = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
        String sql = "insert into zxhd (category,title,content,author,phone,email,time) values(?,?,?,?,?,?,?)";
		try {
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, category);
			statement.setString(2, title);
			statement.setString(3, content);
			statement.setString(4, author);
			statement.setString(5, phone);
			statement.setString(6, email);
			statement.setString(7, txtDate);
		
			   int i = statement.executeUpdate();
			   if (i>0) {
			    add = true;
			   };
			    
			  } catch (SQLException e) {
			   e.printStackTrace();
			  }finally {
			   if (rs != null) {
			    try {
			     rs.close();
			    } catch (SQLException e) {
			     e.printStackTrace();
			    }
			   }
			   if (statement != null) {
			    try {
			     statement.close();
			    } catch (SQLException e) {
			     e.printStackTrace();
			    }
			   }
			   if(connection != null) {
			    try {
			     connection.close();
			    } catch (SQLException e) {
			     e.printStackTrace();
			    }
			   }
			   dbUtil.close();
			  }
			System.out.println("return前的add："+add);
			  return add;
		
		
	}
	public int findCountByLb(String lbString) {
		List<Zxhd> list = new ArrayList<Zxhd>();
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		int count = 0;
		String sql = "select count(*) totalCount from zxhd where category =? ";
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, lbString);
			rs = statement.executeQuery();
			if (rs.next()) {
				count = rs.getInt("totalCount");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return count;

	}
	public List<Zxhd> findAllByLbForFY(String lbString, Page page) {
		List<Zxhd> list = new ArrayList<Zxhd>();
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		String sql = "select * from zxhd where category =?  order by time desc limit ?,? ";
		

		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, lbString);
			statement.setInt(2, page.getBeginIndex());
			statement.setInt(3, page.getEveryPage());
			rs = statement.executeQuery();
			while (rs.next()) {
				Zxhd newBean = new Zxhd();
				newBean.setCategory(rs.getString("category"));
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setEmail(rs.getString("email"));
				newBean.setPhone(rs.getString("phone"));
				newBean.setTime(rs.getDate("time"));

				list.add(newBean);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;

	}
	 public List<Zxhd> Proselect(String lbString,String tilString,Page page) {
		    List<Zxhd> list =new ArrayList<Zxhd>();
		    DBUtil dbUtil = new DBUtil();
		    connection = dbUtil.geConnection();
		    String sql ="select * from zxhd where category =? and concat(id,title,content,categoryname,author,phone,time,email) like ? order by time desc limit ?,?" ;
		    connection = dbUtil.geConnection();
		    try {
		     statement = connection.prepareStatement(sql);
		     statement.setString(1, lbString);
		     statement.setString(2,"%" + tilString + "%");
		     statement.setInt(3, page.getBeginIndex());
		     statement.setInt(4, page.getEveryPage());
		     rs = statement.executeQuery();
		     while(rs.next()) {
		    	 Zxhd zxhd = new Zxhd();
		    	 zxhd.setId(rs.getInt("id"));
		    	 zxhd.setTitle(rs.getString("title"));
		    	 zxhd.setContent(rs.getString("content"));
		    	 zxhd.setCategory(rs.getString("category"));
		    	 zxhd.setAuthor(rs.getString("author"));
		    	 zxhd.setCategoryname(rs.getString("categoryname"));
		    	 zxhd.setTime(rs.getDate("time"));
		    	 zxhd.setPhone(rs.getString("phone"));
		      list.add(zxhd);
		     }
		     
		    } catch (SQLException e) {
		     e.printStackTrace();
		    }finally {
		     if (rs != null) {
		      try {
		       rs.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if (statement != null) {
		      try {
		       statement.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if(connection != null) {
		      try {
		       connection.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     dbUtil.close();
		    }
		    return list;
		    
		   }
	 public List<Zxhd> findbackByLbForFY(String lbString,Page page) {
			List<Zxhd> list = new ArrayList<Zxhd>();
			DBUtil dbUtil = new DBUtil();
			connection = dbUtil.geConnection();
			String sql = "select * from zxhd where category =? order by time desc limit ?,? ";
			try {
				statement = connection.prepareStatement(sql);
				statement.setString(1, lbString);
				statement.setInt(2, page.getBeginIndex());
				statement.setInt(3, page.getEveryPage());
				rs = statement.executeQuery();
				while(rs.next()) {
					Zxhd zxhd = new Zxhd();
					zxhd.setCategory(rs.getString("category"));
					zxhd.setId(rs.getInt("id"));
					zxhd.setTitle(rs.getString("title"));
					zxhd.setContent(rs.getString("content"));
					zxhd.setAuthor(rs.getString("author"));
					zxhd.setPhone(rs.getString("phone"));
					zxhd.setHomepage(rs.getInt("homepage"));
					zxhd.setTime(rs.getDate("time"));
					zxhd.setCategoryname(rs.getString("categoryname"));
					list.add(zxhd);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (statement != null) {
					try {
						statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				dbUtil.close();
			}
			return list;
			
		}
	 public int findbackCountByLb(String lbString) {
		 	DBUtil dbUtil = new DBUtil();
		 	connection = dbUtil.geConnection();
		 	int count = 0;
		 	String sql = "select count(*) totalCount from zxhd where category =? ";
		 	try {
		 		statement = connection.prepareStatement(sql);
		 		statement.setString(1, lbString);
		 		rs = statement.executeQuery();
		 		if (rs.next()) {
		 			count=rs.getInt("totalCount");
		 		}
		 		
		 	} catch (SQLException e) {
		 		e.printStackTrace();
		 	}finally {
		 		if (rs != null) {
		 			try {
		 				rs.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		if (statement != null) {
		 			try {
		 				statement.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		if(connection != null) {
		 			try {
		 				connection.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		dbUtil.close();
		 	}
		 	return count;}
	//后端模糊查询后总数
		 public int findselectCountByLb(String lbString,String tilString) {
		 	DBUtil dbUtil = new DBUtil();
		 	connection = dbUtil.geConnection();
		 	int count = 0;
		 	String sql = "select count(*) totalCount from zxhd where category =? and concat(id,title,content,category,author,phone,time,email) like ? ";
		 	try {
		 		statement = connection.prepareStatement(sql);
		 		statement.setString(1, lbString);
		 		statement.setString(2,"%" + tilString + "%");
		 		rs = statement.executeQuery();
		 		if (rs.next()) {
		 			count=rs.getInt("totalCount");
		 			System.out.print("totalCount :"+count);
		 		}
		 		
		 	} catch (SQLException e) {
		 		e.printStackTrace();
		 	}finally {
		 		if (rs != null) {
		 			try {
		 				rs.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		if (statement != null) {
		 			try {
		 				statement.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		if(connection != null) {
		 			try {
		 				connection.close();
		 			} catch (SQLException e) {
		 				e.printStackTrace();
		 			}
		 		}
		 		dbUtil.close();
		 	}
		 	return count;
		 	
		 }
		 public boolean doDel(int id) {
				boolean del = false;
				DBUtil dbUtil = new DBUtil();
				connection = dbUtil.geConnection();
				// String sql = "select password from admin where username = " + username + "'";
				String sql = "delete from zxhd where id =?";
				try {
					PreparedStatement statement = connection.prepareStatement(sql);

					statement.setInt(1, id);
					int i = statement.executeUpdate();
					if (i > 0) {
						del = true;
					}

				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (statement != null) {
						try {
							statement.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (connection != null) {
						try {
							connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					dbUtil.close();
				}
				return del;

			}
		 public List<Zxhd> findAllByLbForPX(String lbString) {
				System.out.println("run findAllByLb " + lbString);
				List<Zxhd> list = new ArrayList<Zxhd>();
				DBUtil dbUtil = new DBUtil();
				connection = dbUtil.geConnection();
				String sql = "select * from ssfwzx where category = ? and  order by time desc";
				try {
					statement = connection.prepareStatement(sql);
					statement.setString(1, lbString);
					rs = statement.executeQuery();
					while (rs.next()) {
						Zxhd zxhd = new Zxhd();
						zxhd.setCategory(rs.getString("category"));
						zxhd.setId(rs.getInt("id"));
						zxhd.setTitle(rs.getString("title"));
						zxhd.setContent(rs.getString("content"));
						zxhd.setAuthor(rs.getString("author"));
						zxhd.setPhone(rs.getString("phone"));
						zxhd.setEmail(rs.getString("Email"));
						zxhd.setTime(rs.getDate("time"));
						list.add(zxhd);

					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (statement != null) {
						try {
							statement.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (connection != null) {
						try {
							connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					dbUtil.close();
				}
				return list;

			}
		 public Zxhd findNewsById(int id) {
				System.out.println("run findNewsById : " + id);
				DBUtil dbUtil = new DBUtil();
				connection = dbUtil.geConnection();
				Zxhd zxhd = new Zxhd();
				String sql = "select * from zxhd where id = ?";
				try {
					statement = connection.prepareStatement(sql);
					statement.setInt(1, id);
					rs = statement.executeQuery();
					if (rs.next()) {
						zxhd.setId(rs.getInt("id"));
						zxhd.setTitle(rs.getString("title"));
						zxhd.setContent(rs.getString("content"));
						zxhd.setTime(rs.getDate("time"));
						zxhd.setAuthor(rs.getString("author"));
						zxhd.setPhone(rs.getString("phone"));
						zxhd.setCategory(rs.getString("category"));
						zxhd.setEmail(rs.getString("email"));
						

					}

				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (statement != null) {
						try {
							statement.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (connection != null) {
						try {
							connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					dbUtil.close();
				}
				System.out.println("end findNewsById :");
				return zxhd;

			}
		 public boolean doUpdata(String category, String title, String content, String author,int puton,
					int homepage, String txtDate,String phone,int id) {
				boolean updata = false;
				DBUtil dbUtil = new DBUtil();
				connection = dbUtil.geConnection();
				// String sql = "select password from admin where username = " + username + "'";
				String sql = "update zxhd set category =?,title =?,content =?,author =?,homepage=?,time=?,phone=? where id =?";

				try {
					PreparedStatement statement = connection.prepareStatement(sql);
					statement.setString(1, category);
					statement.setString(2, title);
					statement.setString(3, content);
					statement.setString(4, author);
				
					statement.setInt(5, homepage);
					statement.setString(6, txtDate);
					statement.setString(7, phone);
					statement.setInt(8, id);

					int i = statement.executeUpdate();
					if (i > 0) {
						updata = true;
					}
					System.out.println(updata);
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (statement != null) {
						try {
							statement.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (connection != null) {
						try {
							connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					dbUtil.close();
					
				}
				
				return updata;
			}

		 	
}
